﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO;
using Microsoft.SqlServer.Management.Smo;

namespace DataBaseManagement
{
    public partial class ExecutarSQL : Form
    {
        public ExecutarSQL()
        {
            InitializeComponent();
        }

        private void btnRemover_Click(object sender, EventArgs e)
        {
            for (int i = clbArquivos.CheckedIndices.Count; i > 0; i--)
            {
                clbArquivos.Items.RemoveAt(clbArquivos.CheckedIndices[i-1]);
            }
        }

        private void btnAdicionar_Click(object sender, EventArgs e)
        {
            if (String.IsNullOrEmpty(tbxArquivo.Text) && openFileDialog.ShowDialog() == DialogResult.OK )
            {
                tbxArquivo.Text = openFileDialog.FileName;                
            }
            if (File.Exists(tbxArquivo.Text))
            {
                clbArquivos.Items.Add(new FileInfo(tbxArquivo.Text), true);
            }
            tbxArquivo.Text = "";
        }

        private void clbArquivos_Format(object sender, ListControlConvertEventArgs e)
        {
            e.Value = ((FileInfo)e.ListItem).Name;
        }
        private string ObterSQLDosArquivos()
        {
            StringBuilder sql = new StringBuilder();
            foreach (FileInfo file in clbArquivos.Items)
            {
                sql.Append(File.OpenText(file.FullName).ReadToEnd() + Environment.NewLine + "GO" + Environment.NewLine);
            }
            return sql.ToString();
        }
        private void btnExecutar_Click(object sender, EventArgs e)
        {
            string sql = "";
            if (tabControl.SelectedIndex == 0)
            {
                sql = tbxSQL.Text;
            }
            else
            {
                sql = ObterSQLDosArquivos();
            }

            Server servidor = Negocio.ObterServidor(Properties.Settings.Default.Servidor);
            int qtd = 0;
            for (int i = 0; i < dgvAlunos.SelectedRows.Count; i++)
            {
                servidor.ConnectionContext.BeginTransaction();
                try
                {

                    Usuarios.AlunosRow linha = ((Usuarios.AlunosRow)((DataRowView)dgvAlunos.SelectedRows[i].DataBoundItem).Row);
                    if (linha != null && servidor.Databases.Contains(linha.NomeDoBanco))
                    {
                        Database database = servidor.Databases[linha.NomeDoBanco];
                        database.ExecuteNonQuery(sql);                        
                    }
                    servidor.ConnectionContext.CommitTransaction();
                    qtd++;
                }
                catch (Exception erro)
                {
                    servidor.ConnectionContext.RollBackTransaction();
                    MessageBox.Show(erro.ToString());
                }
            }
            MessageBox.Show(qtd.ToString() + " banco(s) atualizado(s).");
        }

        private void btnVisualizar_Click(object sender, EventArgs e)
        {
            tbxSQL.Text = ObterSQLDosArquivos();
            tabControl.SelectedIndex = 0;

        }

        private void ExecutarSQL_Load(object sender, EventArgs e)
        {
            this.dgvAlunos.DataSource = UsuariosNG.ObterAlunos();
        }
    }
}
